package com.model;

import com.entity.Student;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
@Scope("singleton")
public class StudentService {
    @Resource
    DBBase dbBase;

    static String SELECTALL = "select * from student";
    static String SELECTONE = "select * from student where id = ?";
    static String INSERTONE = "insert into student(xh, name, class_name, score) values(?,?,?,?)";
    static String DELETEONE = "delete from student where id = ?";
    static String UPDATEONE = "update student set xh = ?, name = ?, class_name = ?, score = ? where id = ?";

    public List<Student> listAll() throws SQLException, ClassNotFoundException {
        List<Student> list = new ArrayList<>();
        ResultSet rs = dbBase.selectAll(SELECTALL);
        while(rs.next()){
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setXh(rs.getString("xh"));
            student.setName(rs.getString("name"));
            student.setClassName(rs.getString("class_name"));
            student.setScore(rs.getFloat("score"));
            list.add(student);
        }
        return list;
    }

    public List<Student> listOne(Integer id) throws SQLException, ClassNotFoundException {
        List<Student> list = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = id;
        ResultSet rs = dbBase.selectOne(SELECTONE,params);
        while(rs.next()){
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setXh(rs.getString("xh"));
            student.setName(rs.getString("name"));
            student.setClassName(rs.getString("class_name"));
            student.setScore(rs.getFloat("score"));
            list.add(student);
        }
        return list;
    }

    public void AddOne(Student student) throws SQLException, ClassNotFoundException {
        Object[] params = new Object[4];
        params[0] = student.getXh();
        params[1] = student.getName();
        params[2] = student.getClassName();
        params[3] = student.getScore();
        dbBase.InsertOne(INSERTONE,params);
    }

    public void DropOne(Integer id) throws SQLException, ClassNotFoundException {
        Object[] params = new Object[1];
        params[0] = id;
        dbBase.DeleteOne(DELETEONE,params);
    }

    public void SetOne(Student student) throws SQLException, ClassNotFoundException {
        Object[] params = new Object[5];
        params[0] = student.getXh();
        params[1] = student.getName();
        params[2] = student.getClassName();
        params[3] = student.getScore();
        params[4] = student.getId();
        dbBase.UpdateOne(UPDATEONE,params);
    }



    public DBBase getDbBase() {
        return dbBase;
    }
}
